package com.excu.mapper;

import com.excu.entity.Column;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface ColumnsMapper extends BaseMapper<Column> {

    // 用@SelectProvider指定SQL提供类和方法
    @SelectProvider(type = ColumnsSqlProvider.class, method = "getColumnsSql")
    List<Column> getColumns(@Param("tableName") String tableName, @Param("dbName") String dbName);

    @SelectProvider(type = ColumnsSqlProvider.class, method = "getOneSql")
    Column getOne(@Param("tableName") String tableName, @Param("dbName") String dbName);

    @Select("SELECT table_name AS tableName, " +
            "table_comment AS tableComment " +
            "FROM INFORMATION_SCHEMA.TABLES " +
            "WHERE table_schema = #{databaseName}")
    List<Column> getAllTables(@Param("databaseName") String databaseName);

    @SelectProvider(type = ColumnsSqlProvider.class, method = "getMapDataSql")
    List<Map<String, Object>> getMapData(@Param("dbName") String dbName,
                                         @Param("tableName") String tableName,
                                         @Param("offset") Integer offset,
                                         @Param("pageNumber") Integer pageNumber);

    @Select("SELECT count(*) FROM ${dbName}.${tableName}")
    Integer getCount(@Param("dbName") String dbName, @Param("tableName") String tableName);

    @Select("SELECT DISTINCT a.column_name AS columnName, " +
            "a.column_comment AS columnComment, " +
            "a.data_type AS columnType, " +
            "a.IS_NULLABLE AS isNull, " +
            "a.COLUMN_KEY AS isKey, " +
            "a.COLUMN_DEFAULT AS defaultValue, " +
            "a.table_name AS tableName, " +
            "a.CHARACTER_MAXIMUM_LENGTH AS columnLength, " +
            "b.table_comment AS tableComment, " +
            "b.AUTO_INCREMENT AS autoIncrement " +
            "FROM information_schema.columns a " +
            "RIGHT JOIN information_schema.tables b " +
            "ON a.table_name = b.table_name " +
            "WHERE b.table_schema = #{dbName}")
    List<Column> getAllTables2(@Param("dbName") String dbName);


    // 定义SQL提供类，内部用Java代码拼接动态SQL
    class ColumnsSqlProvider {

        // 对应getColumns方法的动态SQL
        public String getColumnsSql(@Param("tableName") String tableName, @Param("dbName") String dbName) {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT DISTINCT a.column_name AS columnName, ");
            sql.append("a.column_comment AS columnComment, ");
            sql.append("a.data_type AS columnType, ");
            sql.append("a.IS_NULLABLE AS isNull, ");
            sql.append("a.COLUMN_KEY AS isKey, ");
            sql.append("a.COLUMN_DEFAULT AS defaultValue, ");
            sql.append("a.table_name AS tableName, ");
            sql.append("a.CHARACTER_MAXIMUM_LENGTH AS columnLength, ");
            sql.append("b.table_comment AS tableComment, ");
            sql.append("b.AUTO_INCREMENT AS autoIncrement ");
            sql.append("FROM information_schema.columns a ");
            sql.append("RIGHT JOIN information_schema.tables b ");
            sql.append("ON a.table_name = b.table_name ");
            sql.append("WHERE a.table_name = #{tableName} ");

            // 替代<if test="dbName != null and dbName != ''">
            if (dbName != null && !dbName.trim().isEmpty()) {
                sql.append("AND b.table_schema = #{dbName} ");
            }
            return sql.toString();
        }

        // 对应getOne方法的动态SQL
        public String getOneSql(@Param("tableName") String tableName, @Param("dbName") String dbName) {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT table_name AS tableName, ");
            sql.append("table_comment AS tableComment ");
            sql.append("FROM INFORMATION_SCHEMA.TABLES ");
            sql.append("WHERE TABLE_NAME = #{tableName} ");

            if (dbName != null && !dbName.trim().isEmpty()) {
                sql.append("AND table_schema = #{dbName} ");
            }
            return sql.toString();
        }

        // 对应getMapData方法的动态SQL
        public String getMapDataSql(@Param("dbName") String dbName,
                                    @Param("tableName") String tableName,
                                    @Param("offset") Integer offset,
                                    @Param("pageNumber") Integer pageNumber) {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT * FROM ${dbName}.${tableName} ");

            // 替代<if test="offset != null and pageNumber != null and pageNumber != 0">
            if (offset != null && pageNumber != null && pageNumber != 0) {
                sql.append("LIMIT #{offset}, #{pageNumber} ");
            }
            return sql.toString();
        }
    }
}